package com.apes.fn.server.transferVehicle.repository;

import com.apes.fn.server.transferVehicle.model.InnerPickDeliveryUpCar;
import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.scm.masterdata.frame.model.Dept;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;
import java.util.Map;


public interface InnerPickDeliveryUpCarRepository extends CustomRepository<InnerPickDeliveryUpCar, String> {

    @Query(value = "select distinct(other_side_dept_id) from (select * FROM fn_inner_pick_del_up_car t WHERE t.belong_dept_id = :belongDept and t.other_side_dept_id is not null Order By t.begin_date Desc) where rownum <=5", nativeQuery = true)
    List<String> findByBelongDeptAndDesc(@Param("belongDept") String belongDept);

    @Query(value = "select * from (select * FROM fn_inner_pick_del_up_car t WHERE t.belong_dept_id = :belongDept and t.other_side_dept_id is not null Order By t.begin_date Desc) where rownum <=5", nativeQuery = true)
    List<InnerPickDeliveryUpCar> findAllByBelongDeptAndDesc(@Param("belongDept") String belongDept);


    // 待内协调度
    @Query(value = "SELECT t.*  FROM fn_inner_pick_del_up_car t WHERE (t.state = 'return' and t.belong_dept_id = :dept) " +
            "or (t.state = 'create' and t.other_side_dept_id = :dept)", nativeQuery = true)
    List<InnerPickDeliveryUpCar> findAllByDeptAndStateReturn(@Param("dept") String dept);

    // 待内协接收
    @Query(value = "SELECT t.* FROM fn_inner_pick_del_up_car t , fn_inner_pick_del_up_car_item item WHERE item.inner_pick_delivery_up_car = t.id and" +
            " (t.state ='return' or t.state = 'create' ) and t.state = item.node_description and " +
            "( ( item.node_description = 'create' and item.dispatcher_dept_id = :dept  ) or (item.node_description = 'return' and item.dispatcher_dept_id = :dept ) ) ", nativeQuery = true)
    List<InnerPickDeliveryUpCar> findAllByDeptAndStateOther(@Param("dept") String dept);

    List<InnerPickDeliveryUpCar> findByOtherSideDeptAndState(Dept dept, String state);
    @Query(value = "SELECT t.id FROM fn_inner_pick_del_up_car t where t.id not in (:id)", nativeQuery = true)
    List<Map> findAllByIdNotIn(List id);


    // 进行中的内协交接车单
    @Query(value = "SELECT t.* FROM fn_inner_pick_del_up_car t WHERE t.state not in('repeal','confirm') and t.belong_dept_id =:dept" , nativeQuery = true)
    List<InnerPickDeliveryUpCar> findByBelongDept(@Param("dept") String dept);

    @Query("select T from InnerPickDeliveryUpCar T WHERE T.lastUpdatedStamp > :times or (T.id > :id and T.lastUpdatedStamp = :times) order by T.lastUpdatedStamp, T.id asc")
    List<InnerPickDeliveryUpCar> findByLastUpdatedStampAndId(@Param("times") Date times, @Param("id") String id, Pageable pageable);

}
